﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BusinessLogic;
using System.Web.Security;
using System.Data.SqlClient;
using System.Data;

namespace DataAccessLogic
{
    public static class JobDataAccess
    {
        private static Job BindRowToJob(DataRow row)
        {
            Job j = new Job();
            j.ID = DataAccess.CheckForGuidColumn(row, "ID");
            j.Description = DataAccess.CheckForStringColumn(row, "NAME");
            j.UserDate = DataAccess.CheckForDateTimeColumn(row, "USERDATE");
            j.DateAdded = DataAccess.CheckForDateTimeColumn(row, "DATEADDED");
            j.DateChanged = DataAccess.CheckForDateTimeColumn(row, "DATECHANGED");
            j.AddedByID = DataAccess.CheckForGuidColumn(row, "ADDEDBYID");
            j.ChangedByID = DataAccess.CheckForGuidColumn(row, "CHANGEDBYID");
            j.AddedBy = DataAccess.CheckForStringColumn(row, "ADDEDBY");
            j.ChangedBy = DataAccess.CheckForStringColumn(row, "CHANGEDBY");

            return j;
        }

        public static Guid Create(Job job)
        {
            Guid id = Guid.Empty;
            Guid providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());
            string outputParameterName = "@ID";

            if (job != null)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                SqlParameter p = new SqlParameter();
                p.ParameterName = outputParameterName;
                p.DbType = System.Data.DbType.Guid;
                p.Direction = System.Data.ParameterDirection.Output;

                parms.Add(p);
                parms.Add(new SqlParameter("@NAME", job.Description));
                parms.Add(new SqlParameter("@USERDATE", job.UserDate));
                parms.Add(new SqlParameter("@LOCATIONCONSTITUENTNAME", job.Location.Name));
                parms.Add(new SqlParameter("@LOCATIONCONSTITUENTSTATEID", job.Location.Address.State.ID));
                parms.Add(new SqlParameter("@LOCATIONCONSTITUENTSTATEDESCRIPTION", job.Location.Address.State.Abbreviation));
                parms.Add(new SqlParameter("@LOCATIONCONSTITUENTADDRESSBLOCK", job.Location.Address.AddressBlock));
                parms.Add(new SqlParameter("@LOCATIONCONSTITUENTCITY", job.Location.Address.City));
                parms.Add(new SqlParameter("@LOCATIONCONSTITUENTPOSTCODE", job.Location.Address.PostCode));
                parms.Add(new SqlParameter("@LOCATIONCONSTITUENTPHONENUMBER", job.Location.Phone.Number));
                parms.Add(new SqlParameter("@LOCATIONCONSTITUENTPHONEEXTENSION", job.Location.Phone.Extension));
                parms.Add(new SqlParameter("@LOCATIONCONSTITUENTFAX", job.Location.Phones.FirstOrDefault(x => x.PhoneTypeAsString.Equals("Fax")).Number));
                parms.Add(new SqlParameter("@LOCATIONCONSTITUENTEMAIL", job.Location.EmailAddress.EmailAddress));
                parms.Add(new SqlParameter("@LOCATIONCONSTITUENTCONTACT", job.Location.PrimaryContact.Name));
                parms.Add(new SqlParameter("@BILLTOCONSTITUENTNAME", job.BillTo.Name));
                parms.Add(new SqlParameter("@BILLTOCONSTITUENTSTATEID", job.BillTo.Address.State.ID));
                parms.Add(new SqlParameter("@BILLTOCONSTITUENTSTATEDESCRIPTION", job.BillTo.Address.State.Abbreviation));
                parms.Add(new SqlParameter("@BILLTOCONSTITUENTADDRESSBLOCK", job.BillTo.Address.AddressBlock));
                parms.Add(new SqlParameter("@BILLTOCONSTITUENTCITY", job.BillTo.Address.City));
                parms.Add(new SqlParameter("@BILLTOCONSTITUENTPOSTCODE", job.BillTo.Address.PostCode));
                parms.Add(new SqlParameter("@BILLTOCONSTITUENTPHONENUMBER", job.BillTo.Phone.Number));
                parms.Add(new SqlParameter("@BILLTOCONSTITUENTPHONEEXTENSION", job.BillTo.Phone.Extension));
                parms.Add(new SqlParameter("@BILLTOCONSTITUENTFAX", job.BillTo.Phones.FirstOrDefault(x => x.PhoneTypeAsString.Equals("Fax")).Number));
                parms.Add(new SqlParameter("@BILLTOCONSTITUENTEMAIL", job.BillTo.EmailAddress.EmailAddress));
                parms.Add(new SqlParameter("@BILLTOCONSTITUENTCONTACT", job.BillTo.PrimaryContact.Name));   
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                object obj = DataAccess.Create(StoredProcedures.USP_JOB_CREATE, outputParameterName, parms);

                if (obj != null)
                {
                    id = new Guid(obj.ToString());
                }
            }

            return id;
        }

        public static void Delete(Guid jobID)
        {
            DataAccess.Delete(StoredProcedures.USP_JOB_DELETE, jobID);
        }   

        public static void Finalize(Guid jobID)
        {
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@JOBID", jobID));

            DataAccess.Update(StoredProcedures.USP_JOB_FINALIZE, parms);
        }

        public static List<Job> GetAll()
        {
            List<Job> jobs = null;
            
            DataTable dt = DataAccess.Get(StoredProcedures.USP_JOB_GETALL);

            if (dt != null)
            {
                jobs = new List<Job>();

                foreach (DataRow row in dt.Rows)
                {
                    Job j = BindRowToJob(row);
                    j.Location = new Constituent() { Name = DataAccess.CheckForStringColumn(row, "LOCATIONCONSTITUENTNAME") };
                    j.BillTo = new Constituent() { Name = DataAccess.CheckForStringColumn(row, "BILLTOCONSTITUENTNAME") };
                    j.SentToIan = DataAccess.CheckForBooleanColumn(row, "SENTTOIAN");
                    j.SentToJenny = DataAccess.CheckForBooleanColumn(row, "SENTTOJENNY");
                    j.Archived = DataAccess.CheckForBooleanColumn(row, "ARCHIVED");
                    j.ArchiveDate = DataAccess.CheckForDateTimeColumn(row, "ARCHIVEDATE");

                    int serviceReportCount = DataAccess.CheckForIntColumn(row, "SERVICEREPORTCOUNT");
                    j.ServiceReports = new List<ServiceReport>(serviceReportCount);
                                        
                    jobs.Add(j);
                }
            }

            return jobs;
        }

        public static List<Job> GetAllUnarchived()
        {
            List<Job> jobs = null;

            DataTable dt = DataAccess.Get(StoredProcedures.USP_JOB_GETALLUNARCHIVED);

            if (dt != null)
            {
                jobs = new List<Job>();

                foreach (DataRow row in dt.Rows)
                {
                    Job j = BindRowToJob(row);
                    j.Location = new Constituent() { Name = DataAccess.CheckForStringColumn(row, "LOCATIONCONSTITUENTNAME") };
                    j.BillTo = new Constituent() { Name = DataAccess.CheckForStringColumn(row, "BILLTOCONSTITUENTNAME") };
                    j.SentToIan = DataAccess.CheckForBooleanColumn(row, "SENTTOIAN");
                    j.SentToJenny = DataAccess.CheckForBooleanColumn(row, "SENTTOJENNY");
                    j.Archived = DataAccess.CheckForBooleanColumn(row, "ARCHIVED");
                    j.ArchiveDate = DataAccess.CheckForDateTimeColumn(row, "ARCHIVEDATE");

                    int serviceReportCount = DataAccess.CheckForIntColumn(row, "SERVICEREPORTCOUNT");
                    j.ServiceReports = new List<ServiceReport>(serviceReportCount);

                    jobs.Add(j);
                }
            }

            return jobs;
        }

        public static Job GetByID(Guid jobID)
        {
            Job j = null;

            DataTable dt = DataAccess.Get(StoredProcedures.USP_JOB_GETBYID, new List<SqlParameter>{ new SqlParameter("@JOBID", jobID) });

            if (dt != null)
            {
                j = new Job();

                foreach (DataRow row in dt.Rows)
                {
                    j = BindRowToJob(row);

                    Constituent location = new Constituent();
                    location.Name = DataAccess.CheckForStringColumn(row, "LOCATIONCONSTITUENTNAME");
                    location.Address = new Address();
                    location.Address.City = DataAccess.CheckForStringColumn(row, "LOCATIONCITY");
                    location.Address.AddressBlock = DataAccess.CheckForStringColumn(row, "LOCATIONADDRESS");
                    location.Address.PostCode = DataAccess.CheckForStringColumn(row, "LOCATIONPOSTCODE");
                    location.Address.State = new State();
                    location.Address.State.Abbreviation = DataAccess.CheckForStringColumn(row, "LOCATIONSTATE");

                    location.EmailAddress = new Email();
                    location.EmailAddress.EmailAddress = DataAccess.CheckForStringColumn(row, "LOCATIONEMAIL");

                    location.Phones = new List<Phone>();
                    location.Phones.Add(new Phone(DataAccess.CheckForStringColumn(row, "LOCATIONNUMBER")) { Extension = DataAccess.CheckForStringColumn(row, "LOCATIONEXTENSION") });
                    location.Phones.Add(new Phone(DataAccess.CheckForStringColumn(row, "LOCATIONFAX")) { PhoneType = new PhoneType() { Description = "Fax" } });

                    location.PrimaryContact = new Constituent() { Name = DataAccess.CheckForStringColumn(row, "LOCATIONPRIMARYCONTACT") };

                    Constituent billTo = new Constituent();
                    billTo.Name = DataAccess.CheckForStringColumn(row, "BILLTOCONSTITUENTNAME");
                    billTo.Address = new Address();
                    billTo.Address.City = DataAccess.CheckForStringColumn(row, "BILLTOCITY");
                    billTo.Address.AddressBlock = DataAccess.CheckForStringColumn(row, "BILLTOADDRESS");
                    billTo.Address.PostCode = DataAccess.CheckForStringColumn(row, "BILLTOPOSTCODE");
                    billTo.Address.State = new State();
                    billTo.Address.State.Abbreviation = DataAccess.CheckForStringColumn(row, "BILLTOSTATE");

                    billTo.EmailAddress = new Email();
                    billTo.EmailAddress.EmailAddress = DataAccess.CheckForStringColumn(row, "BILLTOEMAIL");

                    billTo.Phones = new List<Phone>();
                    billTo.Phones.Add(new Phone(DataAccess.CheckForStringColumn(row, "BILLTONUMBER")) { Extension = DataAccess.CheckForStringColumn(row, "BILLTOEXTENSION") });
                    billTo.Phones.Add(new Phone(DataAccess.CheckForStringColumn(row, "BILLTOFAX")) { PhoneType = new PhoneType() { Description = "Fax" } });

                    billTo.PrimaryContact = new Constituent() { Name = DataAccess.CheckForStringColumn(row, "BILLTOPRIMARYCONTACT") };

                    j.Location = location; 
                    j.BillTo = billTo;
                    j.SentToIan = DataAccess.CheckForBooleanColumn(row, "SENTTOIAN");
                    j.SentToJenny = DataAccess.CheckForBooleanColumn(row, "SENTTOJENNY");
                    j.Archived = DataAccess.CheckForBooleanColumn(row, "ARCHIVED");
                    j.ArchiveDate = DataAccess.CheckForDateTimeColumn(row, "ARCHIVEDATE");

                    int serviceReportCount = DataAccess.CheckForIntColumn(row, "SERVICEREPORTCOUNT");
                    j.ServiceReports = new List<ServiceReport>(serviceReportCount);                    
                }
            }

            return j;
        }

        public static void SendToIan(Guid jobID)
        {
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@JOBID", jobID));

            DataAccess.Update(StoredProcedures.USP_JOB_SENDTOIAN, parms);
        }

        public static void SendToJenny(Guid jobID)
        {
            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@JOBID", jobID));

            DataAccess.Update(StoredProcedures.USP_JOB_SENDTOJENNY, parms);
        }

        public static void Update(Job job)
        {
            Guid providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());

            List<SqlParameter> parms = new List<SqlParameter>();
            parms.Add(new SqlParameter("@ID", job.ID));
            parms.Add(new SqlParameter("@NAME", job.Description));
            parms.Add(new SqlParameter("@USERDATE", job.UserDate));
            parms.Add(new SqlParameter("@LOCATIONCONSTITUENTNAME", job.Location.Name));
            parms.Add(new SqlParameter("@LOCATIONCONSTITUENTSTATEID", job.Location.Address.State.ID));
            parms.Add(new SqlParameter("@LOCATIONCONSTITUENTSTATEDESCRIPTION", job.Location.Address.State.Abbreviation));
            parms.Add(new SqlParameter("@LOCATIONCONSTITUENTADDRESSBLOCK", job.Location.Address.AddressBlock));
            parms.Add(new SqlParameter("@LOCATIONCONSTITUENTCITY", job.Location.Address.City));
            parms.Add(new SqlParameter("@LOCATIONCONSTITUENTPOSTCODE", job.Location.Address.PostCode));
            parms.Add(new SqlParameter("@LOCATIONCONSTITUENTPHONENUMBER", job.Location.Phone.Number));
            parms.Add(new SqlParameter("@LOCATIONCONSTITUENTPHONEEXTENSION", job.Location.Phone.Extension));
            parms.Add(new SqlParameter("@LOCATIONCONSTITUENTFAX", job.Location.Phones.FirstOrDefault(x => x.PhoneTypeAsString.Equals("Fax")).Number));
            parms.Add(new SqlParameter("@LOCATIONCONSTITUENTEMAIL", job.Location.EmailAddress.EmailAddress));
            parms.Add(new SqlParameter("@LOCATIONCONSTITUENTCONTACT", job.Location.PrimaryContact.Name));
            parms.Add(new SqlParameter("@BILLTOCONSTITUENTNAME", job.BillTo.Name));
            parms.Add(new SqlParameter("@BILLTOCONSTITUENTSTATEID", job.BillTo.Address.State.ID));
            parms.Add(new SqlParameter("@BILLTOCONSTITUENTSTATEDESCRIPTION", job.BillTo.Address.State.Abbreviation));
            parms.Add(new SqlParameter("@BILLTOCONSTITUENTADDRESSBLOCK", job.BillTo.Address.AddressBlock));
            parms.Add(new SqlParameter("@BILLTOCONSTITUENTCITY", job.BillTo.Address.City));
            parms.Add(new SqlParameter("@BILLTOCONSTITUENTPOSTCODE", job.BillTo.Address.PostCode));
            parms.Add(new SqlParameter("@BILLTOCONSTITUENTPHONENUMBER", job.BillTo.Phone.Number));
            parms.Add(new SqlParameter("@BILLTOCONSTITUENTPHONEEXTENSION", job.BillTo.Phone.Extension));
            parms.Add(new SqlParameter("@BILLTOCONSTITUENTFAX", job.BillTo.Phones.FirstOrDefault(x => x.PhoneTypeAsString.Equals("Fax")).Number));
            parms.Add(new SqlParameter("@BILLTOCONSTITUENTEMAIL", job.BillTo.EmailAddress.EmailAddress));
            parms.Add(new SqlParameter("@BILLTOCONSTITUENTCONTACT", job.BillTo.PrimaryContact.Name));
            parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

            DataAccess.Update(StoredProcedures.USP_JOB_UPDATE, parms);
        }
    }
}